--exec  sp_load_all_lot_qty_discrepancy_records 

ALTER                      proc sp_load_all_lot_qty_discrepancy_records
  as
  begin
  
  	declare @results Table( ProcessStep_Id int,
  				LotID bigint,
  				LotNumber varchar(50),
  				MfgPartNumber varchar(50),
  				ProcessStep varchar(50),
  				VendorName varchar(50),
  				PrevToolQty int,
  				PrevDateIn varchar(20),
  				CurrentToolQty int,
  				CurrentDateIn varchar(20),
  				QtyDiff int, 
  				Type varchar(20),
 				Vendor_Lot varchar(1000),
				PrevWaferQty int,
				CurrentWaferQty int,
				WaferQtyDiff int,
				Is_Hold bit,
				Ps_type_Id int)
  
  	declare @lot_id bigint
  	declare @stage_id int
  	declare @stage_name varchar(50)
  	declare @stage_position varchar(50)
  	declare @lot_number varchar(50)
  	declare @part_number varchar(50)
  	declare @process_step_id bigint
  	declare @process_step_name varchar(50)
  	declare @vendor_name varchar(50)
  	declare @old_qty int
  	declare @old_qty_date_in datetime
  	declare @new_qty int
  	declare @new_qty_date_in datetime
  	declare @last_record_created_time datetime
  	declare @prev_record_created_time datetime
  	declare @time varchar(40)
  	declare @qty_diff int
  	declare @check_lot_id int
  	declare @processStep_id int
  	declare @lot_current_status varchar(20)
 
 	declare @vendor_lot varchar(300)
	declare @is_hold bit
	declare @Ps_type_Id int
	declare @PS_Type varchar(50)
  	/*
	-- Added by Basha (05/31/2004)
  	declare @temp_var_fab varchar(100) 
  	set  @temp_var_fab = 'Fab'
  	
  
  	declare lot_cursor CURSOR for
  	select product.processStep_ID,lot_id, lot_number, part_number, l_type, l_vendor_name,Lot_Current_Status, ISNULL(Lot_Table.Is_Hold, 1)  
  	from Lot_Table, Location, Product 
  	where (lot_current_status = 'Running' or lot_current_status = 'Transit' ) 
  		and lot_table.location_id = location.location_id 
  		and lot_table.processStep_id = product.processStep_id  
		and ( Product.P_Type is null or Product.P_Type = 'BOARD' or Product.P_Type = 'KIT' ) 
  		order by lot_table.timestamp
  	-- select * from transit_lot_table
  	open lot_cursor
  
  		fetch next from lot_cursor 
  		into @processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name,@lot_current_status, @is_hold 
  		
  		while @@fetch_status = 0
  		begin	
			
			
  			if @lot_current_status = 'Transit'
  			begin
  				select @new_qty=TLT_Ship_Qty,@last_record_created_time=TLT_Ship_Date from transit_lot_table where TLT_Lot_ID = @lot_id
  				insert into @results(ProcessStep_Id,LotID,LotNumber,MfgPartNumber,ProcessStep,VendorName,CurrentToolQty,CurrentDateIn, Is_Hold) values(@processStep_id,@lot_id, @lot_number, @part_number, 'INTRANSIT', @vendor_name, @new_qty, convert(varchar,@last_record_created_time,101), @is_hold) 
  			end
  			else
  			begin	
				set @Ps_type_Id = 
				CASE 
					WHEN @process_step_name='FAB' THEN 1
					WHEN @process_step_name='Wafer Store' THEN 2
					WHEN @process_step_name='SORT' THEN 3
					WHEN @process_step_name='Die Bank' THEN 4
					WHEN @process_step_name='Assembly' THEN 5
					WHEN @process_step_name='TEST' THEN 6
					WHEN @process_step_name='Class Store' THEN 7
					WHEN @process_step_name='Mark & Pack' or @process_step_name = 'Mark &amp; Pack' THEN 8
					WHEN @process_step_name='Finished Goods' THEN 9
					ELSE 0
				END
				
  				if @process_step_name = 'Fab'
  				begin					
  
  					select @last_record_created_time=FL_Created_On, @new_qty=FL_Input_Quantity , @vendor_lot = FL_Vendor_Lot_Number from fab_lot where lot_id = @lot_id
  					select @check_lot_id =Lot_ID, @prev_record_created_time=Created_On, @old_qty=Input_Quantity from fn_return_previous_lot_info(@lot_id, @process_step_name)
  					if @check_lot_id is not null
  					begin
  						select @time = convert(varchar, max( Timestamp ), 101) from fab_stage_lot where lot_id = @lot_id
  						select @stage_id = Stage_ID, @stage_position = FSL_Stage_Position from fab_stage_lot where lot_id = @lot_id and convert(varchar, fab_stage_lot.Timestamp, 101) = @time
  						select @stage_name = FST_Stage_Name from fab_stage_table where stage_id = @stage_id
  	
  							
  						if @old_qty = 0 or @new_qty = 0	-- skip
  						begin	-- dummy update
  							update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  						end
  		
  						else
  						begin
  							--if @old_qty  = @new_qty --Commented by Rohan on July 23rd 2004
  						
  							if @old_qty <> @new_qty
  							begin
  								set @qty_diff =  @new_qty - @old_qty
  								
  								-- Added by Basha (05/31/2004)
  								if ( @stage_name is not null)								
  								begin
  									set @temp_var_fab = @temp_var_fab + '/' + @stage_name
  								end
  								if ( @stage_position is not null)
  								begin
  									set @temp_var_fab = @temp_var_fab + '(' + @stage_position + ')'
  								end
  
  								-- Commented by Basha (05/31/2004)
  								--insert into @results values(@processStep_id,@lot_id, @lot_number, @part_number, 'Fab/' + @stage_name + ' (' + @stage_position + ')', @vendor_name, @old_qty, convert(varchar, @prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null)
  								--insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot) values(@processStep_id,@lot_id, @lot_number, @part_number, @temp_var_fab , @vendor_name, @old_qty, convert(varchar, @prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null,@vendor_lot)
								insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevWaferQty , PrevDateIn, CurrentWaferQty , CurrentDateIn, WaferQtyDiff , Type, Vendor_Lot, Is_Hold, Ps_type_Id) values(@processStep_id,@lot_id, @lot_number, @part_number, @temp_var_fab , @vendor_name, @old_qty, convert(varchar, @prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null,@vendor_lot, @is_hold, @Ps_type_Id)
  								set @temp_var_fab = 'Fab'	
  							end
  		
  							else
  							begin	-- dummy update
  								update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  							end
  						end
  					end
  					/*
  					else 
  					begin
  						insert into @results values(@processStep_id,@lot_id, @lot_number, @part_number, 'Fab/' + @stage_name + ' (' + @stage_position + ')', @vendor_name, '', '', @new_qty, @last_record_created_time,'')
  					end
  					*/
  				end	
				else if @process_step_name = 'Wafer Store' 
  				     or @process_step_name = 'Die Bank' 
  				begin
  					
					declare @preWaferQty int
					declare @currentWaferQty int
					declare @waferQtyDiff int

  					select @last_record_created_time=inventory_lot.Timestamp, @new_qty=IL_Qty_In, @vendor_lot = IL_Vendor_Lot_Number , @currentWaferQty = isNull(IL_Wafer_Qty_In , 0) from inventory_lot where lot_id = @lot_id
  					select @check_lot_id = Lot_ID, @prev_record_created_time=Created_On, @old_qty = Input_Quantity from fn_return_previous_lot_info(@lot_id,'Inventory')

					select top 1 @preWaferQty = isNull(IL_Wafer_Qty_In , 0) from inventory_lot_history where lot_id = @lot_id order by inventory_lot_history.Timestamp desc

  					if @check_lot_id is not null
  					begin
  						if @old_qty = -1 or @new_qty = -1	-- skip
  						begin	-- dummy update
  							update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  						end
  	
  						else
  						begin
  		
  							if ( @old_qty <> @new_qty or @preWaferQty <> @currentWaferQty )
  							begin
  								set @qty_diff = @new_qty - @old_qty
								set @waferQtyDiff = @currentWaferQty - @preWaferQty
  								-- insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot) values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null,@vendor_lot) 
								insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot, PrevWaferQty , CurrentWaferQty , WaferQtyDiff, Is_Hold, Ps_type_Id ) 
								values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101), @qty_diff, null ,@vendor_lot, @preWaferQty, @currentWaferQty, @waferQtyDiff, @is_hold, @Ps_type_Id) 
  							end
  		
  							else
  							begin	-- dummy update
  								update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  							end				
  						end
  					end
  					/*
  					else
  					begin
  						insert into @results values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, '','', @new_qty, convert(varchar,@last_record_created_time,101),'') 
  					end
  					*/
  				end
		
  				else if @process_step_name = 'Class Store' 
  				     or @process_step_name = 'Finished Goods'
				     
  				begin
  					
  					select @last_record_created_time=inventory_lot.Timestamp, @new_qty=IL_Qty_In, @vendor_lot = IL_Vendor_Lot_Number from inventory_lot where lot_id = @lot_id
  					select @check_lot_id = Lot_ID, @prev_record_created_time=Created_On, @old_qty= Input_Quantity from fn_return_previous_lot_info(@lot_id,'Inventory')
  	
  					if @check_lot_id is not null
  					begin
  						if @old_qty = -1 or @new_qty = -1	-- skip
  						begin	-- dummy update
  							update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  						end
  	
  						else
  						begin
  		
  							if @old_qty <> @new_qty
  							begin
  								set @qty_diff = @new_qty - @old_qty
  								insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot, Is_Hold, Ps_type_Id) values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null,@vendor_lot, @is_hold, @Ps_type_Id) 
  							end
  		
  							else
  							begin	-- dummy update
  								update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  							end				
  						end
  					end
  					/*
  					else
  					begin
  						insert into @results values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, '','', @new_qty, convert(varchar,@last_record_created_time,101),'') 
  					end
  					*/
  				end
  				
  				else if @process_step_name = 'Sort'
  				begin
  					
  					select @last_record_created_time=Timestamp, @new_qty=SL_wafer_Qty , @vendor_lot = SL_Vendor_Lot_Number from sort_lot where lot_id = @lot_id
  					select @check_lot_id=Lot_ID, @prev_record_created_time=Created_On, @old_qty=Input_Quantity from fn_return_previous_lot_info(@lot_id,@process_step_name)
  					
  					if @check_lot_id is not null
  					begin
  						if @old_qty = -1 or @new_qty = -1	-- skip
  						begin	-- dummy update
  							update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  						end
  	
  						else
  						begin
  	
  							if @old_qty <> @new_qty
  							begin
  								set @qty_diff = @new_qty - @old_qty
  								-- insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot) values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null,@vendor_lot) 
								insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevWaferQty , PrevDateIn, CurrentWaferQty , CurrentDateIn, WaferQtyDiff , Type, Vendor_Lot, Is_Hold, Ps_type_Id) values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null,@vendor_lot, @is_hold, @Ps_type_Id) 
  							end
  		
  							else
  							begin	-- dummy update
  								update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  							end
  	
  						end
  					end
  					/*
  					else
  					begin
  						insert into @results values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, '','', @new_qty, convert(varchar,@last_record_created_time,101),'') 
  					end
  					*/
  				end
  	
  				else if @process_step_name = 'Assembly'
  				begin
  					
  					select @last_record_created_time=Timestamp, @new_qty=AL_Qty_In , @vendor_lot = AL_Vendor_Lot_Number from assembly_lot where lot_id = @lot_id
  					
  					--select Timestamp, AL_Qty_In from assembly_lot where lot_id = 3730
  					select @check_lot_id= Lot_ID, @prev_record_created_time=Created_On, @old_qty= Input_Quantity from fn_return_previous_lot_info(@lot_id,@process_step_name)
  					--select @check_lot_id= Lot_ID from fn_assembly_return_previous_lot_info(@lot_id)
  					
  					if @check_lot_id is not null
  					begin
  						if @old_qty = -1 or @new_qty = -1	-- skip
  						begin	-- dummy update
  							update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  						end
  	
  						else
  						begin
  	
  							if @old_qty <> @new_qty
  							begin
  								set @qty_diff = @new_qty - @old_qty
  								insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot, Is_Hold, Ps_type_Id) values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty,convert(varchar, @last_record_created_time,101),@qty_diff, null, @vendor_lot, @is_hold, @Ps_type_Id) 
  							end
  			
  							else
  							begin	-- dummy update
  								update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  							end
  						end
  					end
  					/*
  					else if @check_lot_id is null
  					begin
  						print ' default insert'
  						insert into @results values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, '','', @new_qty, convert(varchar,@last_record_created_time,101),'') 
  					end
  					*/
  				end
  				
				else if @process_step_name = 'Mark & Pack' or  @process_step_name = 'Mark &amp; Pack'
  				begin
  					
  					select @last_record_created_time=Timestamp, @new_qty=IL_Qty_In , @vendor_lot = IL_Vendor_Lot_Number from Inventory_lot where lot_id = @lot_id
  					--select Timestamp, AL_Qty_In from assembly_lot where lot_id = 3730
  					select @check_lot_id= Lot_ID, @prev_record_created_time=Created_On, @old_qty= Input_Quantity from fn_return_previous_lot_info(@lot_id,'inventory')
  					--select @check_lot_id= Lot_ID from fn_assembly_return_previous_lot_info(@lot_id)
  					
  					if @check_lot_id is not null
  					begin
  						if @old_qty = -1 or @new_qty = -1	-- skip
  						begin	-- dummy update
  							update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  						end
  	
  						else
  						begin
  	
  							if @old_qty <> @new_qty
  							begin
  								set @qty_diff = @new_qty - @old_qty
  								insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot, Is_Hold, Ps_type_Id) values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty,convert(varchar, @last_record_created_time,101),@qty_diff, null, @vendor_lot, @is_hold, @Ps_type_Id) 
  							end
  			
  							else
  							begin	-- dummy update
  								update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  							end
  						end
  					end
  					
  				end

  				else if @process_step_name = 'Test'
  				begin
  					
  					select @last_record_created_time=Timestamp, @new_qty=FTL_Qty_In ,@vendor_lot = FTL_Vendor_Lot_Number from final_test_lot where lot_id = @lot_id
  					select @check_lot_id=Lot_ID, @prev_record_created_time=Created_On, @old_qty= Input_Quantity from fn_return_previous_lot_info(@lot_id,@process_step_name)
  					if @check_lot_id is not null
  					begin
  						if @old_qty = -1 or @new_qty = -1	-- skip
  						begin	-- dummy update
  							update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  						end
  	
  						else
  						begin
  	
  							if @old_qty <> @new_qty
  							begin
  								set @qty_diff =@new_qty - @old_qty
  								insert into @results(ProcessStep_Id, LotID, LotNumber, MfgPartNumber, ProcessStep, VendorName, PrevToolQty, PrevDateIn, CurrentToolQty, CurrentDateIn, QtyDiff, Type, Vendor_Lot, Is_Hold, Ps_type_Id) values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, @old_qty,convert(varchar,@prev_record_created_time,101), @new_qty, convert(varchar,@last_record_created_time,101),@qty_diff, null, @vendor_lot, @is_hold, @Ps_type_Id) 
  							end
  		
  							else
  							begin	-- dummy update
  								update inventory_lot set IL_Qty_In = 0 where lot_id = -1
  							end
  						end
  					end
  					/*
  					else
  					begin
  						insert into @results values(@processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name, '','', @new_qty, convert(varchar,@last_record_created_time,101),'') 
  					end
  					*/
  				end
  			   
  				set @old_qty = null
  				set @new_qty = null
  	
  				set @old_qty_date_in = null
  				set @new_qty_date_in = null
  				set @qty_diff = null
  				set @check_lot_id = null
				set @is_hold = null
  			end		
  		fetch next from lot_cursor 
  		into @processStep_id,@lot_id, @lot_number, @part_number, @process_step_name, @vendor_name,@lot_current_status, @is_hold 
  	end
  
  	close lot_cursor
  	deallocate lot_cursor
	*/  
  	
  	---- Miscellaneous Table records
  
  	declare @start_date datetime
  	declare @end_date datetime
  	declare @date_time datetime
  
  	declare @temp_varchar varchar(20)
  	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
  	set @start_date = convert(datetime, @temp_varchar)
  	
  	set @temp_varchar = null
  	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
  	set @end_date = convert(datetime, @temp_varchar)
  	declare @type varchar(50)
	declare @wafer_qty_in int

	declare miscellaneousLotCursor cursor for
	
  	select Lot_Number, Part_Number, PS_Type, Vendor_Name, Qty_in, Wafer_Qty_in, convert(varchar, current_timestamp, 101), 'MISCELLANEOUS' , MLT_Vendor_Lot_Number from Miscellaneous_Lot_Table where (Miscellaneous_Lot_Table.Timestamp between @start_date and @end_date) and ( Status = 'Running' or Status = 'Hold' or Status = 'Transit' ) and (PS_Type_Name = 'Fab' or PS_Type_Name = 'Sort' or PS_Type_Name = 'Wafer Store' OR PS_Type_Name = 'Die Bank' OR PS_Type_Name = 'Assembly' or PS_Type_Name = 'Test' or PS_Type_Name = 'Class Store' or PS_Type_Name = 'Finished Goods' or PS_Type_Name ='Mark & Pack' OR PS_Type_Name ='Mark &amp; Pack') 
	open miscellaneousLotCursor
	fetch next from miscellaneousLotCursor into @lot_number, @part_number, @PS_Type, @vendor_name, @new_qty, @wafer_qty_in, @time, @type, @vendor_lot
	while @@fetch_status = 0
 	begin
	
		set @Ps_type_Id = 
		CASE 
			WHEN @PS_Type='FAB' THEN 1
			WHEN @PS_Type='Wafer Store' THEN 2
			WHEN @PS_Type='SORT' THEN 3
			WHEN @PS_Type='Die Bank' THEN 4
			WHEN @PS_Type='Assembly' THEN 5
			WHEN @PS_Type='TEST' THEN 6
			WHEN @PS_Type='Class Store' THEN 7
			WHEN @PS_Type='Mark & Pack' or @PS_Type = 'Mark &amp; Pack' THEN 8
			WHEN @PS_Type='Finished Goods' THEN 9
			ELSE 0
		END
		
		insert into @results( LotNumber, MfgPartNumber, ProcessStep, VendorName, CurrentToolQty, CurrentWaferQty, CurrentDateIn, Type, Vendor_Lot, Ps_type_Id) values
		(@lot_number, @part_number, @PS_Type, @vendor_name, @new_qty, @wafer_qty_in, @time ,'MISCELLANEOUS', @vendor_lot, @Ps_type_Id)

		fetch next from  miscellaneousLotCursor into @lot_number, @part_number, @PS_Type, @vendor_name, @new_qty, @wafer_qty_in,  @time, @type, @vendor_lot
	end
	close miscellaneousLotCursor
	deallocate miscellaneousLotCursor
	  	
  	select * from @results order by Ps_type_Id
  
  	
  end
  
